package com.example.excelStudy;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.base.Splitter;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import javafx.util.Pair;
import lombok.Data;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.StringEscapeUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import sun.misc.VM;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.math.MathContext;
import java.time.LocalDateTime;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
@Data
class a{
    private Boolean b = false;

    public a(Boolean b) {
        this.b = b;
    }
}
public class ExcelSelectTemp {
    public static void main(String[] args) throws Exception {
        RetentionTimeHandVO a = new RetentionTimeHandVO();
        RetentionTimeHandVO b = new RetentionTimeHandVO();
        HashMap<RetentionTimeHandVO, String> map = Maps.newHashMap();
        map.put(a,"呵呵");
        System.out.println(map.get(a));
        System.out.println(map.get(b));
        // RetentionTimeHandVO vo = JSON.toJavaObject(jsonObject, RetentionTimeHandVO.class);
        // LocalDateTime time = vo.getTime();
        // System.out.println(time.getHour());
        // try {
        //     String s = JSON.toJSONString(vo);
        //
        // }catch (Exception e){
        //     System.out.println(e.getMessage());
        // }

        // System.out.println(vo.toString());
        // Pattern patternFloat = Pattern.compile("^(\\d+)|(\\d+)([\\.]\\d{1,2}+)$");
        // boolean matches = patternFloat.matcher("0.").matches();
        // System.out.println(matches);

        // String unit;
        // BigDecimal bigDecimal = BigDecimal.valueOf(2.00);
        // StringBuilder builder = new StringBuilder();
        // if("".equals(builder.toString())){
        //     System.out.println("yes");
        // }
        // System.err.println(bigDecimal.stripTrailingZeros().toPlainString());

        // System.out.println(p.matcher("1.11").matches());

        // File file = new File("/zhangboExcel1.xls");
        // FileOutputStream outputStream = new FileOutputStream(file);
        // HSSFWorkbook workbook = new HSSFWorkbook();
        // HSSFSheet excelSheet = workbook.createSheet("测试级联下拉");
        //
        //
        // //demo 级联下拉
        // //demo 级联下拉
        // Map<String, List<String>> data = Maps.newHashMap();
        // data.put("维度1", Lists.newArrayList("权重","加减分"));
        // data.put("维度2", Lists.newArrayList("非权重"));
        // addValidationToSheet(workbook, excelSheet, data, 'B', 'C', 1, 200);
        //
        // workbook.write(outputStream);
        //
        // outputStream.close();

    }
    private static Name createName(Workbook workbook, String nameName, String formula) {
        Name name = workbook.createName();
        name.setNameName(nameName);
        name.setRefersToFormula(formula);
        return name;
    }
    /**
     * 不可数字开头
     *
     * @param name
     * @return
     */
    static String formatNameName(String name) {
        name = name.replaceAll(" ", "").replaceAll("-", "_").replaceAll(":", ".");
        if (Character.isDigit(name.charAt(0))) {
            name = "_" + name;
        }

        return name;
    }

    /**
     * 给sheet页，添加下拉列表
     *
     * @param workbook    excel文件，用于添加Name
     * @param targetSheet 级联列表所在sheet页
     * @param options     级联数据 ['百度','阿里巴巴']
     * @param column      下拉列表所在列 从'A'开始
     * @param fromRow     下拉限制开始行
     * @param endRow      下拉限制结束行
     */
    public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, Object[] options, char column, int fromRow, int endRow) {
        String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
        Sheet optionsSheet = workbook.createSheet(hiddenSheetName);
        String nameName = column + "_parent";

        int rowIndex = 0;
        for (Object option : options) {
            int columnIndex = 0;
            Row row = optionsSheet.createRow(rowIndex++);
            Cell cell = row.createCell(columnIndex++);
            cell.setCellValue(option.toString());
        }

        createName(workbook, nameName, hiddenSheetName + "!$A$1:$A$" + options.length);

        DVConstraint constraint = DVConstraint.createFormulaListConstraint(nameName);
        CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, (int) column - 'A', (int) column - 'A');
        targetSheet.addValidationData(new HSSFDataValidation(regions, constraint));
    }

    /**
     * 给sheet页  添加级联下拉列表
     *
     * @param workbook    excel
     * @param targetSheet sheet页
     * @param options     要添加的下拉列表内容  ， keys 是下拉列表1中的内容，每个Map.Entry.Value 是对应的级联下拉列表内容
     * @param keyColumn   下拉列表1位置
     * @param valueColumn 级联下拉列表位置
     * @param fromRow     级联限制开始行
     * @param endRow      级联限制结束行
     */
    public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, Map<String, List<String>> options, char keyColumn, char valueColumn, int fromRow, int endRow) {
        String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
        Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);
        List<String> firstLevelItems = new ArrayList<>();

        int rowIndex = 0;
        for (Map.Entry<String, List<String>> entry : options.entrySet()) {
            String parent = formatNameName(entry.getKey());
            firstLevelItems.add(parent);
            List<String> children = entry.getValue();

            int columnIndex = 0;
            Row row = hiddenSheet.createRow(rowIndex++);
            Cell cell = null;

            for (String child : children) {
                cell = row.createCell(columnIndex++);
                cell.setCellValue(child);
            }

            char lastChildrenColumn = (char) ((int) 'A' + children.size() - 1);
            createName(workbook, parent, String.format(hiddenSheetName + "!$A$%s:$%s$%s", rowIndex, lastChildrenColumn, rowIndex));

            DVConstraint constraint = DVConstraint.createFormulaListConstraint("INDIRECT($" + keyColumn + "1)");
            CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, valueColumn - 'A', valueColumn - 'A');
            targetSheet.addValidationData(new HSSFDataValidation(regions, constraint));
        }

        addValidationToSheet(workbook, targetSheet, firstLevelItems.toArray(), keyColumn, fromRow, endRow);

    }



}
